<?php

declare(strict_types=1);
/**
 * This file is part of habo_new_media.
 *
 * @link     https://www.berlen.cn/
 * @contact  tech@berlen.cn
 * @license  http://gitlab.rehou.cn/habo/habo_new_media/-/blob/master/README.md
 */
namespace app\command;

use app\enum\CommonEnum;
use app\enum\ProviderContractEnum;
use app\model\AccountApplyDetailModel;
use app\model\ClientProductModel;
use app\model\CostMediaModel;
use app\model\CostModel;
use app\model\KsFundDailyflowsModel;
use app\model\ProviderContractDealModel;
use app\model\ProviderContractModel;
use app\model\ProviderModel;
use app\model\RechargeModel;
use think\console\Input;
use think\console\Output;
use think\Exception;
use think\facade\Db;

class CostStatWork extends BaseWork
{
    protected function configure()
    {
        // 指令配置
        $this->setName('cost_stat_work');
        $this->setDescription('月初自动生成成本【废弃】');
    }

    protected function execute(Input $input, Output $output)
    {
        set_time_limit(0);
        ini_set('memory_limit', '-1');
        $output->writeln('=======月初自动生成成本开始' . date('Y-m-d H:i:s', time()) . '=======');

        $startDate = date('Y-m-01', strtotime('-1 month'));
        $endDate = date('Y-m-t', strtotime('-1 month'));
        $month = date('Y-m', strtotime('-1 month'));
        $list = $this->_getTypeList();

        try {
            $providerList = $this->_getProviderList();

            if (! $providerList) {
                throw new Exception('没有可用的供应商！');
            }

            foreach ($list as $k => $v) {
                if ($k == ProviderContractEnum::SERVICE_YY) {// 运营
                } elseif ($k == ProviderContractEnum::SERVICE_CY) {// 创意
                } elseif (in_array($k, [ProviderContractEnum::SERVICE_XG, ProviderContractEnum::SERVICE_PP])) {// 效果、品牌
                    foreach ($providerList as $provider) {
                        $contractList = $this->_getContractInfo($k, $provider['id'], $startDate, $endDate);

                        if (! $contractList) {
                            $contractList = $this->_getContractDealInfo($k, $provider['id'], $startDate, $endDate);
                        }

                        if (! $contractList) {
                            throw new Exception('没有可用的合同和协议！');
                        }

                        /*$accountList = $this->_getAccountTransPortList($provider['id']);
                        if ($accountList) {
                            $oldAccountList = $this->_getAccountList($provider['id'], array_unique(array_column($accountList, 'advertiser_id')));
                            $accountList = array_merge($accountList, $oldAccountList);
                        }*/

                        $clientList = $this->_getClientList($provider['id']);
                        if (! $clientList) {
                            throw new Exception('没有可用的客户！');
                        }

                        foreach ($contractList as $contract) {
                            foreach ($clientList as $client) {
                                $serviceList = $this->_getServiceList($client['client_id']);
                                if (! $serviceList) {
                                    continue;
                                }

                                $serviceIds = array_column($serviceList, 'service_id');
                                if (! in_array($k, $serviceIds)) {
                                    echo '成本类型：' . $v . ',供应商名称：' . $provider['provider_name'] . ' 无客户或无此服务' . PHP_EOL;
                                    continue;
                                }

                                if ($contract['product_type'] == 1) {
                                    $productIds = ClientProductModel::column('id');
                                } else {
                                    $productIds = json_decode($contract['product_ids'], true);
                                }

                                $accountList = $this->_getAccountList($client['client_id'], $client['media_id'], $provider['id'], $productIds);
                                $transAccountList = $this->_getAccountTransPortList($provider['id']);

                                if ($transAccountList) {
                                    $accountList = array_merge($accountList, $transAccountList);
                                }

                                if (! $accountList) {
                                    echo '成本类型：' . $v . ',供应商名称：' . $provider['provider_name'] . ',客户：' . $client['client_name'] . ',合同：' . $contract['contract_name'] . ',媒体1为' . CommonEnum::MEDIA_NAME[$client['media_id']] . '下无账号' . PHP_EOL;
                                    continue;
                                }

                                $accountCost = 0;
                                $actualRefundAmount = 0;
                                if ($contract['statement_type'] == 1) {
                                    foreach ($accountList as $key => $item) {
                                        $recharge = $this->_getRechargeInfo($item['advertiser_id'], $startDate, $endDate);

                                        if ($recharge['sum_actual_money'] <= 0 && $recharge['actual_amount'] <= 0) {
                                            unset($accountList[$key]);
                                            continue;
                                        }
                                        $accountList[$key]['cost'] = $recharge['sum_actual_money']; // 实充现金金额
                                        $accountList[$key]['real_charged'] = $recharge['actual_amount']; // 实充显账金额

                                        $accountCost += $item['real_charged'];
                                        // 退款记录
                                        $refund = $this->_getRefundInfo($item['advertiser_id'], $startDate, $endDate);
                                        $accountList[$key]['refund_amount'] = $refund['refund_amount']; // 实充现金金额
                                        $accountList[$key]['actual_refund_amount'] = $refund['actual_refund_amount']; // 实充显账金额
                                        $actualRefundAmount += $accountList[$key]['actual_refund_amount'];

                                        if ($accountCost > 0) {
                                            $info = (new CostModel())->where([
                                                ['provider_id', '=', $provider['id']],
                                                ['provider_contract_id', '=', $contract['contract_id']],
                                                ['provider_contract_service_id', '=', $contract['id']],
                                                ['client_id', '=', $client['client_id']],
                                                ['cost_month', '=', $month],
                                            ])->find();

                                            if ($info) {
                                                echo '成本类型：' . $v . ',供应商名称：' . $provider['provider_name'] . ',客户：' . $client['client_name'] . ',合同：' . $contract['contract_name'] . ',媒体2为' . CommonEnum::MEDIA_NAME[$client['media_id']] . '已存在' . PHP_EOL;
                                            }

                                            if ($contract['service_policy_type'] == ProviderContractEnum::FAN_LI) {
                                                if ($contract['statement_type'] == 1) {
                                                    $accountCost = round(($accountCost - $actualRefundAmount) / (1 + ($contract['have_rate'] / 100)), 3);
                                                } else {
                                                    $accountCost = round($accountCost / (1 + ($contract['have_rate'] / 100)), 3);
                                                }
                                            } elseif ($contract['service_policy_type'] == ProviderContractEnum::FAN_XIAN) {
                                                if ($contract['statement_type'] == 1) {
                                                    $accountCost = round(($accountCost - $actualRefundAmount) * (1 - ($contract['have_rate'] / 100)), 3);
                                                } else {
                                                    $accountCost = round($accountCost * (1 - ($contract['have_rate'] / 100)), 3);
                                                }
                                            }

                                            $insertId = (new CostModel())->insertGetId([
                                                'service_type' => $contract['service_type'],
                                                'company_id' => $provider['company_id'],
                                                'subject_id' => $contract['subject_id'],
                                                'cost_type' => $k,
                                                'cost_month' => $month,
                                                'cost_explain' => $v . '-' . $client['client_name'] . '-' . $month . '-' . $k . '成本',
                                                'provider_id' => $provider['id'],
                                                'provider_contract_id' => $contract['contract_id'],
                                                'provider_contract_service_id' => $contract['id'],
                                                'client_id' => $client['client_id'],
                                                'amount' => $accountCost,
                                                'create_staff_id' => 0,
                                                'status' => -2,
                                                'create_time' => time(),
                                            ]);
                                            if ($insertId > 0) {
                                                echo '添加成功' . PHP_EOL;
                                            }

                                            $allData = [];
                                            foreach ($accountList as $item) {
                                                if ($item['media_id'] == CommonEnum::QQ_MEDIA) {
                                                    continue;
                                                }

                                                $allData[] = [
                                                    'cost_id' => $insertId,
                                                    'media_id' => $item['media_id'],
                                                    'account_name' => $item['account_name'],
                                                    'advertiser_id' => $item['advertiser_id'],
                                                    'cost' => $item['cost'],
                                                    'real_charged' => $item['real_charged'],
                                                    'refund_amount' => ! empty($item['refund_amount']) ? $item['refund_amount'] : 0,
                                                    'actual_refund_amount' => ! empty($item['actual_refund_amount']) ? $item['actual_refund_amount'] : 0,
                                                    'create_time' => time(),
                                                ];

                                                /*(new CostMediaModel())->insertGetId(
                                                    [
                                                        'cost_id' => $insertId,
                                                        'media_id' => $item['media_id'],
                                                        'account_name' => $item['account_name'],
                                                        'advertiser_id' => $item['advertiser_id'],
                                                        'cost' => $item['cost'],
                                                        'real_charged' => $item['real_charged'],
                                                        'refund_amount' => ! empty($item['refund_amount']) ? $item['refund_amount'] : 0,
                                                        'actual_refund_amount' => ! empty($item['actual_refund_amount']) ? $item['actual_refund_amount'] : 0,
                                                        'create_time' => time(),
                                                    ]
                                                );*/
                                            }

                                            if ($allData) {
                                                (new CostMediaModel())->insertAll($allData, 1000);
                                            }
                                        }
                                    }
                                } else {
                                    foreach ($accountList as $key => $item) {
                                        if ($item['media_id'] == CommonEnum::KS_MEDIA) {
                                            $info = $this->_getKsFundDayFlows($item['advertiser_id'], $startDate, $endDate);

                                            if ($info['daily_charge'] <= 0) {
                                                //  unset($accountList[$key]);
                                                continue;
                                            }

                                            $accountList[$key]['cost'] = $info['daily_charge'];
                                            $accountList[$key]['real_charged'] = $info['daily_charge'] - $info['contract_rebate_real_charged'] - $info['direct_rebate_real_charged'];
                                            $accountCost += $accountList[$key]['real_charged'];
                                        }
                                    }
                                }

                                if ($accountCost > 0) {
                                    $info = (new CostModel())->where([
                                        ['provider_id', '=', $provider['id']],
                                        ['provider_contract_id', '=', $contract['contract_id']],
                                        ['provider_contract_service_id', '=', $contract['id']],
                                        ['cost_month', '=', $month],
                                    ])->find();

                                    if ($info) {
                                        echo '成本类型：' . $v . ',供应商名称：' . $provider['provider_name'] . ',客户：' . $client['client_name'] . ',合同：' . $contract['contract_name'] . ',媒体5为' . CommonEnum::MEDIA_NAME[$client['media_id']] . '已存在' . PHP_EOL;
                                    }

                                    if ($contract['service_policy_type'] == ProviderContractEnum::FAN_XIAN) {
                                        $accountCost = round($accountCost * ($contract['service_have_rate'] / 100), 3);
                                    } elseif ($contract['service_policy_type'] == ProviderContractEnum::FAN_LI) {
                                        $accountCost = round($accountCost - ($accountCost / (1 + ($contract['service_have_rate'] / 100))), 3);
                                    }

                                    $insertId = (new CostModel())->insertGetId([
                                        'service_type' => $contract['service_type'],
                                        'company_id' => $provider['company_id'],
                                        'subject_id' => $contract['subject_id'],
                                        'cost_type' => $k,
                                        'cost_month' => $month,
                                        'cost_explain' => $v . '-' . $client['client_name'] . '-' . $month . '-' . $k . '成本',
                                        'provider_id' => $provider['id'],
                                        'provider_contract_id' => $contract['contract_id'],
                                        'provider_contract_service_id' => $contract['id'],
                                        'client_id' => $client['client_id'],
                                        'amount' => $accountCost,
                                        'create_staff_id' => 0,
                                        'status' => -2,
                                        'create_time' => time(),
                                    ]);

                                    if ($insertId > 0) {
                                        echo '添加成功' . PHP_EOL;
                                    }

                                    $allData = [];
                                    foreach ($accountList as $item) {
                                        if ($item['media_id'] == CommonEnum::QQ_MEDIA) {
                                            continue;
                                        }

                                        $allData[] = [
                                            'cost_id' => $insertId,
                                            'media_id' => $item['media_id'],
                                            'account_name' => $item['account_name'],
                                            'advertiser_id' => $item['advertiser_id'],
                                            'cost' => $item['cost'],
                                            'real_charged' => $item['real_charged'],
                                            'create_time' => time(),
                                        ];

                                        /*(new CostMediaModel())->insertGetId([
                                            'cost_id' => $insertId,
                                            'media_id' => $item['media_id'],
                                            'account_name' => $item['account_name'],
                                            'advertiser_id' => $item['advertiser_id'],
                                            'cost' => $item['cost'],
                                            'real_charged' => $item['real_charged'],
                                            'create_time' => time(),
                                        ]);*/
                                    }

                                    if ($allData) {
                                        (new CostMediaModel())->insertAll($allData, 1000);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (\Exception $e) {
            $output->writeln('=======月初自动生成成本失败' . $e->getMessage() . date('Y-m-d H:i:s', time()) . '=======');
        }

        $output->writeln('=======月初自动生成成本结束' . date('Y-m-d H:i:s', time()) . '=======');
    }

    private function _getKsFundDayFlows(string $advId = '', string $startDate = '', string $endDate = '')
    {
        return KsFundDailyflowsModel::where('advertiser_id', $advId)
            ->whereBetween('cost_date', [$startDate, $endDate])
            ->field(['sum(`daily_charge`)' => 'daily_charge', 'sum(`contract_rebate_real_charged`)' => 'contract_rebate_real_charged', 'sum(`direct_rebate_real_charged`)' => 'direct_rebate_real_charged'])
            ->find();
    }

    private function _getRefundInfo(string $advId = '', string $startDate = '', string $endDate = '')
    {
        $startTime = strtotime($startDate);
        $endTime = strtotime($endDate . ' 23:59:59');
        $sql = "SELECT
                    sum( a.amount ) AS `refund_amount`,
                    sum( a.actual_amount ) AS `actual_refund_amount` 
                FROM
                    `habo_refund` `r`
                    LEFT JOIN `habo_refund_advertiser` `a` ON `a`.`refund_id` = `r`.`id` 
                WHERE
                    `r`.`
                    create_time` BETWEEN {$startTime} 
                    AND {$endTime} 
                    AND ( a.advertiser_id = '{$advId}' AND r.STATUS = " . RechargeModel::STATUS_END . ' ) 
                    LIMIT 1';

        return Db::query($sql);
    }

    private function _getRechargeInfo(string $advId = '', string $startDate = '', string $endDate = '')
    {
        $startTime = strtotime($startDate);
        $endTime = strtotime($endDate . ' 23:59:59');
        $sql = "SELECT
                    sum( a.actual_money ) AS `sum_actual_money`,
                    sum( a.actual_amount ) AS `actual_amount` 
                FROM
                    `habo_recharge` `r`
                    LEFT JOIN `habo_recharge_advertiser` `a` ON `a`.`recharge_id` = `r`.`id` WHERE `r`.`create_time` BETWEEN {$startTime} 
                    AND {$endTime} 
                    AND ( a.advertiser_id = '{$advId}' AND r.STATUS = " . RechargeModel::STATUS_END . ' ) 
                    LIMIT 1';

        return Db::query($sql);
    }

    private function _getAccountList(int $clientId = 0, int $mediaId = 0, int $providerId = 0, array $productIds = [])
    {
        $sql = "SELECT
                    `a`.`client_id`,
                    `a`.`media_id`,
                    `d`.`advertiser_id`,
                    `ca`.`account_name` 
                FROM
                    `habo_account_apply` `a`
                    LEFT JOIN `habo_account_apply_detail` `d` ON `d`.`apply_id` = `a`.`id`
                    LEFT JOIN `habo_port` `p` ON `p`.`id` = `a`.`port_id`
                    LEFT JOIN `habo_client_account` `ca` ON `ca`.`advertiser_id` = `d`.`advertiser_id` 
                WHERE
                    a.client_id = {$clientId} 
                    AND d.advertiser_id IS NOT NULL 
                    AND p.media_id = {$mediaId} 
                    AND p.provider_id = {$providerId} 
                    AND a.product_id IN ( " . implode(',', $productIds) . ' ) 
                GROUP BY
                    `d`.`advertiser_id`';
        return Db::query($sql);
    }

    private function _getServiceList(int $clientId = 0)
    {
        $sql = "SELECT
                    `service_id` 
                FROM
                    `habo_client_contract` `c`
                    LEFT JOIN `habo_client_contract_service` `s` ON `c`.`id` = `s`.`contract_id` 
                WHERE
                     c.client_id = {$clientId}  
                GROUP BY
                    `s`.`service_id`";

        return Db::query($sql);
    }

    private function _getClientList(int $providerId = 0): array
    {
        $sql = "SELECT
                    c.id AS client_id,
                    ca.advertiser_id,
	                ca.media_id,
                    c.client_short_name AS client_name 
                FROM
                    `habo_account_apply` `ac`
                    LEFT JOIN `habo_port` `p` ON `p`.`id` = `ac`.`port_id`
                    LEFT JOIN `habo_account_apply_detail` `a` ON `a`.`apply_id` = `ac`.`id`
                    LEFT JOIN `habo_client_account` `ca` ON `ca`.`advertiser_id` = `a`.`advertiser_id`
                    LEFT JOIN `habo_client` `c` ON `c`.`id` = `ca`.`client_id`
                WHERE
                    ( p.provider_id = {$providerId} AND a.`status` = " . AccountApplyDetailModel::STATUS_ENABLE . ' AND c.id IS NOT NULL ) 
                    and ca.media_id != ' . CommonEnum::QQ_MEDIA . '
                GROUP BY
                    `c`.`id`';
        return Db::query($sql);
    }

    private function _getAccountTransPortList(int $providerId = 0): array
    {
        $sql = "SELECT
                    a.client_id,
                    t.media_id,
                    t.advertiser_id,
                    a.account_name
                FROM
                    habo_account_transfer AS t
                    INNER JOIN habo_client_account AS a ON t.advertiser_id = a.advertiser_id 
                WHERE
                    t.provider_id = {$providerId} 
                    AND t.set_time <= '" . date('Y-m-d') . "'" .
            ' 
                    AND t.media_id != ' . CommonEnum::QQ_MEDIA;
        return Db::query($sql);
    }

    private function _getContractDealInfo(int $serviceId = 0, int $providerId = 0, string $startDate = '', string $endDate = '')
    {
        $sql = "SELECT
                `c`.`contract_name`,
                `c`.`subject_id`,
                `s`.*,
                s.deal_id contract_id,
                2 service_type 
            FROM
                `habo_provider_contract_deal` `c`
                LEFT JOIN `habo_provider_contract_deal_service` `s` ON `s`.`deal_id` = `c`.`id` 
            WHERE
                (
                    s.service_id = {$serviceId} 
                    AND c.provider_id = {$providerId} 
                    AND s.id IS NOT NULL 
                    AND s.product_type IS NOT NULL 
                    AND c.`status` = " . ProviderContractDealModel::STATUS_ENABLE . " 
                    AND c.contract_start_date <= '{$startDate}' AND c.contract_end_date >= '{$endDate}' 
                ) 
            GROUP BY
                `s`.`id`";
        return Db::query($sql);
    }

    private function _getContractInfo(int $serviceId = 0, int $providerId = 0, string $startDate = '', string $endDate = '')
    {
        $sql = "SELECT
                    `c`.`contract_name`,
                    `c`.`subject_id`,
                    `s`.*,
                    1 service_type 
                FROM
                    `habo_provider_contract` `c`
                    LEFT JOIN `habo_provider_contract_service` `s` ON `s`.`contract_id` = `c`.`id` 
                WHERE
                    (
                        s.service_id = {$serviceId} 
                        AND c.provider_id = {$providerId} 
                        AND s.id IS NOT NULL 
                        AND s.product_type IS NOT NULL 
                        AND c.`status` = " . ProviderContractModel::STATUS_ENABLE . "
                        AND c.contract_start_date <= '{$startDate}' AND c.contract_end_date >= '{$endDate}' 
                    ) 
                GROUP BY
                    `s`.`id`";
        return Db::query($sql);
    }

    private function _getProviderList(): array
    {
        return (new ProviderModel())->where('status', ProviderModel::STATUS_ENABLE)->where('id', 299)->select()->toArray();
    }

    private function _getTypeList()
    {
        return ProviderContractEnum::CONTRACT_SERVICE;
    }
}
